using System;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Collections.Generic;
using Dapper;
using MediatR;
using NPOI.XSSF.UserModel;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.SalesReport;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesReport
{
    /// <summary>
    /// 导出 发货通知明细表 处理器
    /// </summary>
    public class ExportDeliveryNoticeDetailHandler : IRequestHandler<ExportDeliveryNoticeDetailCommand, byte[]>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public ExportDeliveryNoticeDetailHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<byte[]> Handle(ExportDeliveryNoticeDetailCommand request, CancellationToken cancellationToken)
        {
            using var conn = _dbConnectionFactory.CreateConnection();

            var where = new List<string>
            {
                "dn.IsDeleted = 0",
                "(dni.IsDeleted = 0 OR dni.Id IS NULL)"
            };
            var p = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(request.SalesOrderCode))
            {
                where.Add("dn.Sales_order_code LIKE @SalesOrderCode");
                p.Add("@SalesOrderCode", $"%{request.SalesOrderCode.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.CustomerName))
            {
                where.Add("c.CustomerName LIKE @CustomerName");
                p.Add("@CustomerName", $"%{request.CustomerName.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.MaterialName))
            {
                where.Add("(dni.Material_name LIKE @MaterialName OR m.MaterName LIKE @MaterialName)");
                p.Add("@MaterialName", $"%{request.MaterialName.Trim()}%");
            }
            if (request.StartDate.HasValue)
            {
                where.Add("dn.Bill_date >= @StartDate");
                p.Add("@StartDate", request.StartDate.Value.Date);
            }
            if (request.EndDate.HasValue)
            {
                where.Add("dn.Bill_date <= @EndDate");
                p.Add("@EndDate", request.EndDate.Value.Date.AddDays(1).AddSeconds(-1));
            }

            var whereClause = where.Count > 0 ? ("WHERE " + string.Join(" AND ", where)) : string.Empty;

            var sql = $@"
SELECT 
    dn.Bill_code AS BillCode,
    c.CustomerName AS CustomerName,
    u.UserName AS SalesmanName,
    dn.Bill_date AS BillDate,
    COALESCE(dni.Material_code, m.MaterCode) AS MaterialCode,
    COALESCE(dni.Material_name, m.MaterName) AS MaterialName,
    sod.Num AS OrderQty,
    dni.Qty AS DeliveryQty
FROM Delivery_notice dn
LEFT JOIN Customer c ON dn.Customer_id = c.Id AND c.IsDeleted = 0
LEFT JOIN User u ON dn.Salesman_id = u.Id AND u.IsDeleted = 0
LEFT JOIN Delivery_notice_item dni ON dn.Id = dni.Delivery_notice_id AND (dni.IsDeleted = 0 OR dni.Id IS NULL)
LEFT JOIN Mater m ON (dni.Material_id = m.Id OR (dni.Material_code IS NOT NULL AND dni.Material_code = m.MaterCode)) AND m.IsDeleted = 0
LEFT JOIN SalesOrder so ON so.OrderCode = dn.Sales_order_code AND so.IsDeleted = 0
LEFT JOIN SalesOrderDetail sod ON sod.SalesOrderId = so.Id AND m.Id IS NOT NULL AND sod.MaterId = m.Id AND sod.IsDeleted = 0
{whereClause}
ORDER BY dn.Bill_date DESC, dn.Id DESC, dni.Id ASC";

            var rows = (await conn.QueryAsync(sql, p)).ToList();

            var wb = new XSSFWorkbook();
            var sheet = wb.CreateSheet("发货通知明细");

            var header = new[] { "发货通知号", "客户名称", "销售员", "单据日期", "物料编码", "物料名称", "受订数量", "发货数量", "未出库数量" };
            var headerRow = sheet.CreateRow(0);
            for (int i = 0; i < header.Length; i++)
            {
                headerRow.CreateCell(i).SetCellValue(header[i]);
            }

            int r = 1;
            foreach (IDictionary<string, object> row in rows)
            {
                var order = ToDecimal(row, "OrderQty");
                var delivery = ToDecimal(row, "DeliveryQty");
                var undelivered = Math.Max(0m, order - delivery);

                var rr = sheet.CreateRow(r++);
                rr.CreateCell(0).SetCellValue(row["BillCode"]?.ToString() ?? "");
                rr.CreateCell(1).SetCellValue(row["CustomerName"]?.ToString() ?? "");
                rr.CreateCell(2).SetCellValue(row["SalesmanName"]?.ToString() ?? "");
                rr.CreateCell(3).SetCellValue(FormatDate(row["BillDate"]));
                rr.CreateCell(4).SetCellValue(row["MaterialCode"]?.ToString() ?? "");
                rr.CreateCell(5).SetCellValue(row["MaterialName"]?.ToString() ?? "");
                rr.CreateCell(6).SetCellValue(order.ToString());
                rr.CreateCell(7).SetCellValue(delivery.ToString());
                rr.CreateCell(8).SetCellValue(undelivered.ToString());
            }

            for (int i = 0; i < header.Length; i++) sheet.AutoSizeColumn(i);

            using var ms = new System.IO.MemoryStream();
            wb.Write(ms);
            return ms.ToArray();
        }

        private static decimal ToDecimal(object? v, string? name = null)
        {
            if (v == null) return 0m;
            if (v is decimal d) return d;
            if (decimal.TryParse(v.ToString(), out var r)) return r;
            return 0m;
        }
        private static string FormatDate(object? v)
        {
            if (v == null) return string.Empty;
            if (v is DateTime dt) return dt.ToString("yyyy-MM-dd");
            if (DateTime.TryParse(v.ToString(), out var d)) return d.ToString("yyyy-MM-dd");
            var s = v.ToString() ?? string.Empty;
            return s.Length >= 10 ? s.Substring(0, 10) : s;
        }
    }
}
